﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DAL
{
    public class sqlHelper
    {
        static String _ConnectionString = null;

        static public String FormatStringToSearch(String s)
        {
            String r = String.Empty;
            for (int i = 0; i < s.Length; i++)
            {
                if (s[i] == '*')
                    r += "%"; // replace by *
                else if (s[i] == '?')
                    r += "_";
                else
                    r += s[i].ToString();
            }
            return r;
        }

        public static String GetConnectionString()
        {
            try
            {
                if (_ConnectionString == null)
                    _ConnectionString = 
                        System.Configuration.ConfigurationManager.
                        ConnectionStrings["ConStr_qlnsDB"].ToString();
                return _ConnectionString;
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }

        public static System.Data.DataTable ExecuteQuery(
            String storename, // store name
            List<System.Data.SqlClient.SqlParameter> prs // list params
            )
        {
            System.Data.DataTable dt = new System.Data.DataTable();
            System.Data.SqlClient.SqlConnection conn =
                new System.Data.SqlClient.SqlConnection(GetConnectionString());
            try
            {
                conn.Open();
                System.Data.SqlClient.SqlCommand cm = conn.CreateCommand();
                cm.CommandType = System.Data.CommandType.StoredProcedure;
                cm.CommandText = storename;
                if (prs != null)
                {
                    foreach (System.Data.SqlClient.SqlParameter p in prs)
                    {
                        cm.Parameters.Add(p);
                    }
                }
                System.Data.SqlClient.SqlDataAdapter adp = 
                    new System.Data.SqlClient.SqlDataAdapter();
                adp.SelectCommand = cm;
                adp.Fill(dt);
                conn.Close();
                return dt;
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public static System.Data.DataTable ExecuteQuery(
           System.Data.SqlClient.SqlConnection connection, // must be opened before
           System.Data.SqlClient.SqlTransaction transaction, // must be init before
           String storename, // store name
           List<System.Data.SqlClient.SqlParameter> prs // list params
           )
        {
            if (connection == null || transaction == null ||
                connection.State != System.Data.ConnectionState.Open)
                throw new System.Exception("connection not init");

            System.Data.DataTable dt = new System.Data.DataTable();
            try
            {
                System.Data.SqlClient.SqlCommand cm = new System.Data.SqlClient.SqlCommand();
                cm.Connection = connection;
                cm.Transaction = transaction;
                cm.CommandType = System.Data.CommandType.StoredProcedure;
                cm.CommandText = storename;
                if (prs != null)
                {
                    foreach (System.Data.SqlClient.SqlParameter p in prs)
                    {
                        cm.Parameters.Add(p);
                    }
                }
                System.Data.SqlClient.SqlDataAdapter adp =
                    new System.Data.SqlClient.SqlDataAdapter();
                adp.SelectCommand = cm;
                adp.Fill(dt);
                return dt;
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                
            }
        }

        public static int ExecuteNoneQuery(
            System.Data.SqlClient.SqlConnection connection, // must be opened before
            System.Data.SqlClient.SqlTransaction transaction, // must be init before
            String storename, 
            List<System.Data.SqlClient.SqlParameter> prs
            )
        {
            int r = -1;
            if (connection == null || transaction == null ||
                connection.State != System.Data.ConnectionState.Open)
                throw new System.Exception("connection not init");
            try
            {
                System.Data.SqlClient.SqlCommand cm = new System.Data.SqlClient.SqlCommand();
                cm.Connection = connection;
                cm.Transaction = transaction;
                cm.CommandType = System.Data.CommandType.StoredProcedure;
                cm.CommandText = storename;
                if (prs != null)
                {
                    foreach (System.Data.SqlClient.SqlParameter p in prs)
                    {
                        cm.Parameters.Add(p);
                    }
                }
                System.Data.SqlClient.SqlParameter Retval =
                    cm.Parameters.Add("@Retval", System.Data.SqlDbType.Int);
                Retval.Direction = System.Data.ParameterDirection.ReturnValue;
                r = cm.ExecuteNonQuery();
                return int.Parse(cm.Parameters["@Retval"].Value.ToString());
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {

            }
        }

        public static int ExecuteNoneQuery(
            String storename,
            List<System.Data.SqlClient.SqlParameter> prs
            )
        {
            int r = -1;
            System.Data.SqlClient.SqlConnection conn =
                new System.Data.SqlClient.SqlConnection(GetConnectionString());
            try
            {
                conn.Open();
                System.Data.SqlClient.SqlCommand cm = conn.CreateCommand();
                cm.CommandType = System.Data.CommandType.StoredProcedure;
                cm.CommandText = storename;
                if (prs != null)
                {
                    foreach (System.Data.SqlClient.SqlParameter p in prs)
                    {
                        cm.Parameters.Add(p);
                    }
                }
                System.Data.SqlClient.SqlParameter Retval =
                    cm.Parameters.Add("@Retval", System.Data.SqlDbType.Int);
                Retval.Direction = System.Data.ParameterDirection.ReturnValue;
                r = cm.ExecuteNonQuery();
                r = int.Parse(cm.Parameters["@Retval"].Value.ToString());
                conn.Close();
                return r;
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public static bool TestConnection()
        {
            bool r = false;
            try
            {
                System.Data.SqlClient.SqlConnection con =
                    new System.Data.SqlClient.SqlConnection(GetConnectionString());
                con.Open();
                con.Close();
                r = true;
                return r;
            }
            catch (System.Exception ex)
            {
                throw (ex);
            }
        }
    }
}
